﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace QLHS2010
{
    public class HocSinhDAO : DataProvider
    {
        public HocSinhDAO()
        {
            connect();
        }

        //public ArrayList getDsHocSinh()
        //{
        //    connect();
        //    string query = "SELECT * FROM HOCSINH";
        //    adapter = new OleDbDataAdapter(query, connection);
        //    DataSet dataset = new DataSet();
        //    adapter.Fill(dataset);
        //    ArrayList arr = ConvertDataSetToArrayList(dataset);

        //    return arr;
        //}

        public HocSinhDTO[] getDsHocSinh()
        {
            string query = "SELECT * FROM HOCSINH";
            adapter = new OleDbDataAdapter(query, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            HocSinhDTO[] arr;

            DataTable dt = dataset.Tables[0];
            int i, n = dt.Rows.Count;
            arr = new HocSinhDTO[n];
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr[i] = (HocSinhDTO)hs;
            }
            return arr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            HocSinhDTO hs = new HocSinhDTO();
            hs.MaHS = dt.Rows[i]["MaHocSinh"].ToString();
            hs.TenHS = dt.Rows[i]["TenHocSinh"].ToString();
            hs.DiaChi = dt.Rows[i]["DiaChi"].ToString();
            hs.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
            hs.MaLop = dt.Rows[i]["MaLop"].ToString();
            hs.GioiTinh = dt.Rows[i]["GioiTinh"].ToString();
            hs.NamHoc = int.Parse(dt.Rows[i]["NamHoc"].ToString());

            return (object)hs;
        }

        public void insert(HocSinhDTO info)
        {
            string insertCommand = "INSERT INTO HOCSINH VALUES('" +
                info.MaHS + "', '" +
                info.TenHS + "', '" +
                info.DiaChi + "', '" +
                info.NgaySinh.ToShortDateString() + "', '" +
                info.MaLop + "', '" +
                info.GioiTinh + "'," +
                info.NamHoc + ")";

            executeNonQuery(insertCommand);
        }

        public void update(HocSinhDTO info)
        {
            string updateCommand = "UPDATE HOCSINH " +
                                    "SET TenHocSinh = '" + info.TenHS + "', " +
                                    " DiaChi = '" + info.DiaChi + "', " +
                                    " NgaySinh = '" + info.NgaySinh.ToShortDateString() + "', " +
                                    " MaLop = '" + info.MaLop + "'," +
                                    " GioiTinh = '" + info.GioiTinh + "', " +
                                    " NamHoc = " + info.NamHoc +
                                    " WHERE MaHocSinh = '" + info.MaHS + "'";


            executeNonQuery(updateCommand);
        }

        public void delete(string maHS)
        {
            string deleteCommand = "DELETE FROM HOCSINH WHERE MaHocSinh = '" + maHS + "'";
            executeNonQuery(deleteCommand);
        }
    }
}